package com.gametime.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.gametime.model.AttributeSet;
import com.gametime.model.Player;
import com.gametime.util.DBUtils;
import com.gametime.util.LogUtil;

public class PlayerDAO
{

  private static PlayerDAO instance = null;
  private PlayerDAO()
  {
    
  }
  
  public static PlayerDAO getInstance()
  {
    if(instance == null)
      instance = new PlayerDAO();
    return instance;
  }
  
  public void save
  (
      Player p
  ) throws Exception
  {
    if(p.getPlayerId() < 1)
    {
      createPlayer(p);
    }else
    {
      updatePlayer(p);
    }
  }

  private void createPlayer
  (
      Player p
  ) throws Exception
  {
    Connection conn=null;
    try
    {
      conn = DBUtils.getInstance().getConnection();
      createPlayer(p, conn);
      p.getPerson().setId(getLastId(conn));
      LogUtil.trace(p.getPlayerId());
    }catch(Exception e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
      DBUtils.closeConnection(conn);
    }
  }
  
  private int getLastId(Connection conn)
  throws SQLException
  {
    int result = 0;
    final String sql= "SELECT LAST_INSERT_ID()";
    PreparedStatement ps=null;
    ResultSet rs = null;
    try
    {
      ps = conn.prepareStatement(sql);
      rs = ps.executeQuery();
      while(rs.next())
      {
        result = rs.getInt(1);
      }
      
    }catch(SQLException e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
     rs.close();
     ps.clearParameters();
     ps.close();
    }
    return result;
  }

  private void createPlayer
  (
      Player p, 
      Connection conn
  )throws SQLException
  {
    final AttributeSet set = p.getAttributes();
    final String sql=
        " insert into player "+
            " (                  "+
            "   first_name,  "+
            "   last_name,  "+
            "   ht,  "+
            "   wt,  "+
            "   pos_id, "+ 
            "   agility,  "+
            "   charisma,  "+
            "   desire,  "+
            "   ego,  "+
            "   endurance,  "+
            "   energy,  "+
            "   handle,  "+
            "   health,  "+
            "   intelligence,  "+
            "   karma,  "+
            "   person,  "+
            "   shot_select,  "+
            "   shot_skill,  "+
            "   size,  "+
            "   strength "+
            " ) values "+
            " ( "+
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ?, "+ 
            "   ? "+
            " ) ";
    PreparedStatement ps=null;
    try
    {
      ps = conn.prepareStatement(sql);
      ps.setString(1, p.getFirstName());
      ps.setString(2, p.getLastName());
      ps.setInt(3, p.getRawHeight());
      ps.setInt(4, p.getWeight());
      ps.setInt(5, p.getPosition().getPositionId());
      ps.setInt(6, set.getAgility()); 
      ps.setInt(7, set.getCharisma()); 
      ps.setInt(8, set.getDesire());
      ps.setInt(9, set.getEgo()); 
      ps.setInt(10, set.getEndurance());
      ps.setInt(11,set.getEnergy()); 
      ps.setInt(12,set.getHandle()); 
      ps.setInt(13,set.getHealth());
      ps.setInt(14,set.getIntelligence()); 
      ps.setInt(15,set.getKarma()); 
      ps.setInt(16,set.getPerson());
      ps.setInt(17,set.getShotSelect());
      ps.setInt(18,set.getShotSkill()); 
      ps.setInt(19,set.getSize()); 
      ps.setInt(20,set.getStrength());
      ps.execute();
    }catch(SQLException e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
     ps.clearParameters();
     ps.close();
    }
    
  }

  private void updatePlayer
  (
      Player p
  ) throws Exception
  {
    Connection conn=null;
    try
    {
      conn = DBUtils.getInstance().getConnection();
      updatePlayer(p, conn);
    }catch(Exception e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
      DBUtils.closeConnection(conn);
    }
  }
  
  private void updatePlayer
  (
      Player p, 
      Connection conn
  )throws SQLException
  {
    final AttributeSet set = p.getAttributes();
    final String sql=
        " update player "+
            "  set first_name = ?, "+ 
            "      last_name = ?, "+
            "      ht=?, "+
            "      wt=?,  "+
            "      pos_id=?, "+ 
            "      agility=?,  "+
            "      charisma=?,  "+
            "      desire=?,  "+
            "      ego=?,  "+
            "      endurance=?,  "+
            "      energy=?,  "+
            "      handle=?,  "+
            "      health=?,  "+
            "      intelligence=?,  "+
            "      karma=?,  "+
            "      person=?,  "+
            "      shot_select=?,  "+
            "      shot_skill=?,  "+
            "      size=?,  "+
            "      strength=? "+
            "  where player_id = ? ";
    PreparedStatement ps=null;
    try
    {
      ps = conn.prepareStatement(sql);
      ps.setString(1, p.getFirstName());
      ps.setString(2, p.getLastName());
      ps.setInt(3, p.getRawHeight());
      ps.setInt(4, p.getWeight());
      ps.setInt(5, p.getPosition().getPositionId());
      ps.setInt(6, set.getAgility()); 
      ps.setInt(7, set.getCharisma()); 
      ps.setInt(8, set.getDesire());
      ps.setInt(9, set.getEgo()); 
      ps.setInt(10, set.getEndurance());
      ps.setInt(11,set.getEnergy()); 
      ps.setInt(12,set.getHandle()); 
      ps.setInt(13,set.getHealth());
      ps.setInt(14,set.getIntelligence()); 
      ps.setInt(15,set.getKarma()); 
      ps.setInt(16,set.getPerson());
      ps.setInt(17,set.getShotSelect());
      ps.setInt(18,set.getShotSkill()); 
      ps.setInt(19,set.getSize()); 
      ps.setInt(20,set.getStrength());
      ps.setInt(21,p.getPlayerId());
      ps.execute();
    }catch(SQLException e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
     ps.clearParameters();
     ps.close();
    }
  }

  public void saveTeam(int playerId, int teamId, int seasonId)
  throws Exception
  {
    Connection conn=null;
    try
    {
      conn = DBUtils.getInstance().getConnection();
      saveTeam(playerId, teamId, seasonId, conn);
    }catch(Exception e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
      DBUtils.closeConnection(conn);
    }
    
  }

  private void saveTeam(int playerId, int teamId, int seasonId, Connection conn)
  throws SQLException
  {
    final String sql="insert into team_player(team_id, player_id, start_date, season_id) values (?, ?, sysdate(), ?)";
    PreparedStatement ps=null;
    try
    {
      ps = conn.prepareStatement(sql);
      ps.setInt(1, teamId);
      ps.setInt(2, playerId);
      ps.setInt(3, seasonId);
      ps.execute();
      
    }catch(SQLException e)
    {
      LogUtil.trace(e);
      throw e;
    }finally 
    {
      try
      {
        ps.close();
      }catch(Exception e){}
      
    }
    
  }

}
